import path from 'path';
import * as fs from 'fs';
import Database from 'better-sqlite3';

const DATA_DIR = path.join(process.cwd(), 'excel', '06-21');
const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

// 直接使用 better-sqlite3
const db = new Database(DB_PATH);

interface TableDefinition {
  tableName: string;
  tableCode: string;
  description: string;
  fields: Array<{
    sequence: string;
    name: string;
    example: string;
    dataType: string;
    measurementClass: string;
    dataSource: string;
  }>;
}

interface TableMetadata {
  tableName: string;
  tableCode: string;
  description: string;
  sourceFile: string;
  createdAt: string;
}

function parseCSVFile(filename: string): TableDefinition[] {
  const filePath = path.join(DATA_DIR, filename);
  if (!fs.existsSync(filePath)) {
    console.warn(`文件不存在: ${filePath}`);
    return [];
  }

  let contentBuffer = fs.readFileSync(filePath);
  // 处理 BOM
  if (contentBuffer[0] === 0xEF && contentBuffer[1] === 0xBB && contentBuffer[2] === 0xBF) {
    contentBuffer = contentBuffer.subarray(3);
  }

  const content = contentBuffer.toString('utf-8');
  const lines = content.split('\n').map(line => line.trim()).filter(line => line);
  
  const tables: TableDefinition[] = [];
  let currentTable: TableDefinition | null = null;
  let expectingHeader = true;

  for (let i = 0; i < lines.length; i++) {
    const line = lines[i];
    const columns = line.split(',').map(col => col.trim());

    // 检查是否是表头行（包含"表 C."）
    if (line.includes('表 C.') || line.includes('表C.')) {
      // 保存之前的表
      if (currentTable) {
        tables.push(currentTable);
      }

      // 解析表名信息
      const tableInfo = columns[0];
      // 修复正则表达式，支持 C.109-1 这样的格式
      const match = tableInfo.match(/表\s*C\.?(\d+(?:-\d+)?)\s+([A-Z]+\d+(?:-\d+)?)\s+(.+)/);

      if (match) {
        const [, tableNumber, tableCode, description] = match;
        // 将 109-1 转换为 109_1 作为表名
        const sanitizedTableNumber = tableNumber.replace('-', '_');
        currentTable = {
          tableName: `table_c${sanitizedTableNumber}`,
          tableCode: tableCode,
          description: description,
          fields: []
        };
        expectingHeader = true;
      }
      continue;
    }

    // 检查是否是列名行
    if (expectingHeader && columns[0] === '序号' && columns[1] === '中文名称') {
      expectingHeader = false;
      continue;
    }

    // 处理字段定义行
    if (currentTable && !expectingHeader && columns.length >= 6) {
      const sequence = columns[0];
      const name = columns[1];
      const example = columns[2];
      const dataType = columns[3];
      const measurementClass = columns[4];
      const dataSource = columns[5];

      // 跳过空行或无效行
      if (sequence && name && sequence !== '序号') {
        currentTable.fields.push({
          sequence,
          name,
          example,
          dataType,
          measurementClass,
          dataSource
        });
      }
    }
  }

  // 添加最后一个表
  if (currentTable) {
    tables.push(currentTable);
  }

  return tables;
}

function createParameterMetadataTable() {
  const sql = `
    CREATE TABLE IF NOT EXISTS parameter_table_metadata (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      table_name TEXT NOT NULL UNIQUE,
      table_code TEXT NOT NULL,
      description TEXT NOT NULL,
      source_file TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `;

  db.exec(sql);
  console.log('✅ 创建参数表元数据表');
}

function createParameterTable(tableDef: TableDefinition) {
  // 创建表的 SQL
  const fields = tableDef.fields.map(field => {
    const columnName = field.name.replace(/[^\w\u4e00-\u9fa5]/g, '_');
    const dataType = field.dataType.includes('数值') ? 'REAL' : 'TEXT';
    return `${columnName} ${dataType}`;
  }).join(',\n    ');

  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS ${tableDef.tableName} (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      ${fields},
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `;

  try {
    db.exec(createTableSQL);
    console.log(`✅ 创建表: ${tableDef.tableName} (${tableDef.description})`);

    // 插入元数据
    const insertMetadataSQL = `
      INSERT OR REPLACE INTO parameter_table_metadata
      (table_name, table_code, description, source_file, updated_at)
      VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
    `;

    db.prepare(insertMetadataSQL).run(
      tableDef.tableName,
      tableDef.tableCode,
      tableDef.description,
      'excel/06-21'
    );

  } catch (error) {
    console.error(`❌ 创建表失败: ${tableDef.tableName}`, error);
  }
}

function dropExistingParameterTables() {
  // 删除现有的通用参数表
  const tablesToDrop = [
    'base_parameters',
    'naming_parameters',
    'position_parameters',
    'tech_spec_parameters'
  ];

  tablesToDrop.forEach(tableName => {
    try {
      db.exec(`DROP TABLE IF EXISTS ${tableName}`);
      console.log(`🗑️ 删除旧表: ${tableName}`);
    } catch (error) {
      console.warn(`警告: 删除表 ${tableName} 失败:`, error);
    }
  });
}

async function main() {
  try {
    console.log('🚀 开始重新创建参数表结构...');
    
    // 1. 删除现有的参数表
    dropExistingParameterTables();
    
    // 2. 创建元数据表
    createParameterMetadataTable();
    
    // 3. 解析并创建新的参数表
    const files = [
      '属性表-基础参数表.csv'  // 只处理基础参数表来修复 table_c81
    ];

    let totalTables = 0;
    
    for (const filename of files) {
      console.log(`\n📄 处理文件: ${filename}`);
      const tables = parseCSVFile(filename);
      
      console.log(`发现 ${tables.length} 个表定义:`);
      tables.forEach(table => {
        console.log(`  - ${table.tableName}: ${table.description} (${table.fields.length} 个字段)`);
      });
      
      // 创建表
      tables.forEach(createParameterTable);
      totalTables += tables.length;
    }
    
    console.log(`\n✅ 参数表重建完成!`);
    console.log(`📊 总计创建了 ${totalTables} 个参数表`);
    
    // 显示创建的表列表
    const metadata = db.prepare('SELECT * FROM parameter_table_metadata ORDER BY table_name').all();
    console.log('\n📋 创建的参数表列表:');
    metadata.forEach((meta: any) => {
      console.log(`  ${meta.table_name}: ${meta.description}`);
    });
    
  } catch (error) {
    console.error('❌ 重建参数表失败:', error);
    process.exit(1);
  } finally {
    db.close();
  }
}

main();
